その他
記述の簡略化（With,Set）
1 With 〜 End With ステートメント
同一オブジェクトに対して連続的に処理を行うときは、With
ステートメントを使用することにより、コードの記述を簡略化することができます。
With 〜 End With の内部にさらに別の With 〜 End With を入れて、ネスト (入れ子)
構造にすることもできます。ただし、それぞれの With ステートメントには異なるオブジェクトを指定する必要があります。
With ステートメントは次のように使用します。
With <オブジェクト>
ステートメント
End With
|
1 With ステートメントを用いないコードの記述 |
処理内容：セル内の書式を指定します
Sub with_1()
Worksheets("Sheet1").Select
Range("A1").Value = 100
Range("A1").NumberFormatLocal = "0_ ;[赤]-0 "
Range("A1").Interior.Color = RGB(0, 255, 255)
Range("A1").Font.Name = "MSゴシック"
Range("A1").Font.Italic = True
Range("A1").Font.ColorIndex = xlAutomatic
Range("A1").Font.Size = 12
End Sub
処理内容：セル内の書式を指定します
Sub with_2()
With Worksheets("Sheet1").Range("A1")
.Value = 100
.NumberFormatLocal = "0_ ;[赤]-0 "
.Interior.Color = RGB(0, 255, 255)
.Font.Name = "MSゴシック"
.Font.Italic = True
.Font.ColorIndex = xlAutomatic
.Font.Size = 12
End With
End Sub
処理内容：上記例をWith ステートメントのネストにより記述
Sub with_3()
With Worksheets("Sheet1").Range("A1")
.Value = 100
.NumberFormatLocal = "0_ ;[赤]-0 "
.Interior.Color = RGB(0, 255, 255)
With .Font
.Name = "MSゴシック"
.Italic = True
.ColorIndex = xlAutomatic
.Size = 12
End With
End With
End Sub
2 Set ステートメント
Set ステートメントを使用して変数にオブジェクトへの参照を代入すると、代入したプロシージャ以降、変数を使用してオブジェクトを操作できるようになります。使用する変数は、参照するオブジェクトと同じオブジェクトの種類で宣言されている必要があります。
処理内容：ブックを変数に代入します
Sub set_1()
Dim wb As Workbook
Set wb = Workbooks("Book1.xls")
wb.Worksheets("sheet1").Select
Set wb = Nothing
End Sub
処理内容：ワークシートを変数に代入します
Sub set_2()
Dim ws As Worksheet
Set ws = Workbooks("Book1").Worksheets("sheet1")
ws.Select
Set ws = Nothing
End Sub
処理内容：セル範囲を変数に代入します
Sub set_3()
Dim r1 As Range
Set r1 = Range("A1").CurrentRegion
r1.ClearContents
Set r1 = Nothing
End Sub
処理内容：Set ステートメントと With ステートメントの併用（値と表示形式の転記）
Sub set_4()
Dim ws1 As Worksheet,ws2 As Worksheet
Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")
For R = 1 To 10
With ws2.Cells(R, 1)
.Value = ws1.Cells(R, 1).Value
.NumberFormat = ws1.Cells(R, 1).NumberFormat
End With
Next R
Set ws1 = Nothing
Set ws2 = Nothing
End Sub
マクロの一時中断→再実行
マクロ実行中にデータを入力して、再度マクロの実行を継続したいときなどには、マクロの処理を中断前と再開後の処理の 2 つに分けて、実行します。
マクロの再開には、予めボタンを作成しておいて、マクロを再開したいとき、ボタンをクリックすることにより
2 つ目のマクロを実行するようにします。以下のプログラムは、2
つで 1 セットです。
処理内容：マクロ再実行ボタンを作成します
Public NewBar As Object
Sub Macro1()
'(Macro中断前の処理)
On Error Resume Next
Application.CommandBars("OWARI").Delete
Set NewBar = CommandBars.Add
With NewBar
.Name = "OWARI"
.Visible = True
.Controls.Add Type:=msoControlButton, Before:=1
With .Controls(1)
.Style = msoButtonCaption
.Caption = "入力終了"
End With
End With
NewBar.Controls(1).OnAction = "Macro2"
'(ここでマクロの実行を中断します。入力終了ボタンがクリックされたらMacro2を実行)
End Sub
処理内容：マクロ再実行ボタンを削除する（マクロ再開後の処理）
Sub Macro2()
Application.CommandBars("OWARI").Delete '
ボタンの削除
'(マクロ再開後の処理)
End Sub
警告・確認メッセージの非表示・表示
ファイルを閉じて登録する時などに表示される「警告・確認メッセージ」の非表示・表示を切り替えます。
処理内容：シート削除時に削除確認メッセージを表示させない
Sub Alerts_1()
Application.DisplayAlerts = False '警告・確認メッセージを表示しない
Worksheets("Sheet1").Delete
Application.DisplayAlerts = True '警告・確認メッセージを表示する
End Sub
処理内容：確認メッセージを表示させずにブックを閉じます
Sub Alerts_2()
Workbooks("Book1.xls").Close SaveChanges:=True
End Sub
処理内容：変更があった場合のみブックを保存して閉じる（確認メッセージを表示させず）
Sub Alerts_3()
With ActiveWorkbook
If .Saved = True Then
.Close False
Else
.Close True
End If
End With
End Sub
ブックのイベント
1 イベントマクロとは
Excel
を手作業で操作している場合に、ブックを開く、シートをアクティブにする、セルをクリックする、セルのデータ内容を変更する、などの処理を行うと操作の内容により
Excel の画面の状態が変化します。これは、メニューやツールバーのボタンがクリックされたり、マウスの操作によって
Excel が自動的にそれを感知し、決められた処理を行っているからです。
ここで、「アクティブにする」「変更する」「クリックする」などはイベントと呼ばれます。つまり、Excel
に対して何らかの操作を行うとイベント(処理を実行するきっかけ)が発生していることになります。
イベントマクロとは、「特定の操作を行ったときに自動的に実行されるマクロ」のことをいいます。
イベントマクロは、Excel
を手作業で操作している場合の既定のイベントマクロだけでなく、VBA
マクロを作成して自動的に発生させることができます。
2 ブックで発生するイベント
ブックに対して発生するイベントの種類は下表のとおりです。
| イベントの種類 |
イベントが発生するタイミング |
| Activate |
ブックがアクティブになったとき発生します。 |
| AddinInstall |
ブックがアドインとして組み込まれたときに発生します。 |
| AddinUninstall |
ブックのアドインとして組み込みを解除したときに発生します。 |
| BeforeClose |
ブックを閉じる前に発生します。 |
| BeforePrint |
ブックまたはその中に含まれる内容を印刷する前に発生します |
| BeforeSave |
ブックを保存する前に発生します。 |
| Deactivate |
ブックが非アクティブになったときに発生します。 |
| NewSheet |
新しいシートをブックに作成したときに発生します。 |
| Open |
ブックを開いたときに発生します。 |
| SheetActivate |
シートがアクティブになったときに発生します｡ |
| SheetBeforeDoubleClick |
ワークシートをダブルクリックしたときに発生します。 |
| SheetBeforeRightClick |
ワークシートを右クリックしたときに発生します。 |
| SheetCalculate |
再計算したときに発生します。 |
| SheetChange |
ワークシートのセルの値が変更されたときに発生します。 |
| SheetDeactivate |
シートが非アクティブでになったときに発生します。 |
| SheetFollowHyperlink |
Excel のハイパーリンクをクリックすると発生します。 |
| SheetSelectionChange |
ワークシートで選択範囲を変更したときに発生します。 |
| WindowActivate |
ブックのウィンドウがアクティブになったときに発生します。 |
| WindowDeactivate |
ブックのウィンドウが非アクティブになったときに発生します。 |
| WindowResize |
ブックのウィンドウ サイズを変更したときに発生します。 |
3 ブックのイベントプロシージャの作成
ブックのマクロは、VBE
画面左端のプロジェクトエクスプローラ内にある「This
Workbook」をダブルクリックしたときに表示されるモジュールシートに記述します。
モジュールシート最上部に「(General)」と表示されているところをオブジェクトボックスといいます。ボックスをクリックして「Workbook」をクリックすると、ボックスの右にある「プロシージャボックス」のイベントプロシージャ名が自動的に「Open」になり、モジュールに
Private Sub Workbook_Open()
End Sub
上記「Workbook_Open」プロシージャが自動的に挿入されます。他のイベントプロシージャを作成するときは、プロシージャボックスをクリックして、作成したいプロシージャ名をクリックすると、モジュールシートに挿入されます。
処理内容：ブックが開かれたときに発生するイベント
Private Sub Workbook_Open()
’ ブックが開かれたら、「Sheet2」シートの Range A1を画面左上端に表示する
Application.Goto Worksheets("Sheet2").Range("A1"), True
End Sub
処理内容：新しいシートを作成したときに発生するイベント
Private Sub Workbook_NewSheet(ByVal Sh As Object)
’ 新しいシートをブックの最後に移動させる
Sh.Move After:=Sheets(Sheets.Count)
End Sub
処理内容：ブックがアクティブになったときに発生するイベント
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
’ シート名をメッセージボックスに表示する
MsgBox "このシートの名前は「 " & Sh.Name & " 」です"
End Sub
処理内容：シートをダブルクリックしたときに発生するイベント
Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, _
ByVal Target As Range, Cancel As Boolean)
’ シートをダブルクリックしたら、「Sheet1」シートの Range A1を画面左上端に表示する
Application.Goto Worksheets("Sheet1").Range("A1"), True
Cancel = true
End Sub
処理内容：シートを右クリックしたときに発生するイベント
Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, _
ByVal Target As Range, Cancel As Boolean)
’ シートを右クリックしたら、メッセージボックスにセルの値を表示する
MsgBox Target.Value
Cancel = true
End Sub
処理内容：セルの値が変更されたときに発生するイベント
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal
Target As Range)
’
シート値を変更したら、メッセージボックスにセルのアドレスを表示する
MsgBox Target.Address & " の値を「" &
Target.Value & " 」に変更しました。"
End Sub
処理内容：ワークシートで選択範囲を変更したときに発生するイベント
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _
ByVal Target As Range)
’ シートのセルを選択したら、メッセージボックスにセルのアドレスを表示する
MsgBox "選択したセルは" & Target.Address & " です。"
End Sub
処理内容：シートが非アクティブになったとき発生するイベント
Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
’ 異なるシートを選択したら、メッセージボックスに閉じたシート名を表示する
MsgBox Sh.Name & "を閉じました"
End Sub
シートのイベント
シートに対して発生するイベントの種類は下表のとおりです。イベント・イベントマクロについての内容は、ここをクリックしてください。
| イベントの種類 |
イベントが発生するタイミング |
| Activate |
ブックがアクティブになったとき発生します。 |
| BeforeDoubleClick |
ワークシートをダブルクリックしたときに発生します。 |
| BeforeRightClick |
ワークシートを右クリックしたときに発生します。 |
| Calculate |
ワークシートを再計算したときに発生します。 |
| Change |
ワークシートのセルの値が変更されたときに発生します。 |
| Deactivate |
ワークシートが非アクティブでになったときに発生します。 |
| FollowHyperlink |
ワークシート のハイパーリンクをクリックすると発生します。 |
| SelectionChange |
ワークシートで選択範囲を変更したときに発生します。 |
シートのマクロは、VBE
画面左端のプロジェクトエクスプローラ内にあるシート名の中で、イベントを発生させるシート名をダブルクリックしたときに表示されるモジュールシートに記述します。
モジュールシート最上部に「(General)」と表示されているところをオブジェクトボックスといいます。ボックスをクリックして「Worksheet」をクリックすると、ボックスの右にある「プロシージャボックス」のイベントプロシージャ名が自動的に「SelectionChange」になり、モジュールに
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
End Sub
上記「SelectionChange」プロシージャが自動的に挿入されます。他のイベントプロシージャを作成するときは、プロシージャボックスをクリックして、作成したいプロシージャ名をクリックすると、モジュールシートに挿入されます。
処理内容：ワークシートがアクティブになったときに発生するイベント
Private Sub Worksheet_Activate()
’ シート名をメッセージボックスに表示する
MsgBox "このシートの名前は「 " & ActiveSheet.Name & " 」です"
End Sub
処理内容：ワークシートをダブルクリックしたときに発生するイベント
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel As Boolean)
’指定範囲内をダブルクリックしたとき、「○」が入力されているときは消去し、
' 入力されていないときは該当セルに「○」を入力する。
If Intersect(Target, Range("A1:A20")) Is Nothing Then Exit Sub
If Target.Value = "○" Then
Target.Value = Empty
Else
Target.Value = "○"
End If
Cancel = True
End Sub
処理内容：ワークシートを右クリックしたときに発生するイベント
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, _
Cancel As Boolean)
’A1:A10範囲を右クリックしたら、メッセージを表示し、範囲外のときは終了する。
If Intersect(Target, Range("A1:A10")) Is Nothing Then Exit Sub
MsgBox "選択したセルは A1:A10 の範囲内です。"
Cancel = True
End Sub
処理内容：ワークシートで選択範囲を変更したときに発生するイベント
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
’A列を選択したら、「A列が選択されました」のメッセージを、A列以外が選択されたら
’「A列を選択してください」のメッセージを表示する。
If Target.Column = 1 Then
MsgBox "A列が選択されました"
Else
MsgBox "A列を選択してください"
End If
End Sub
処理内容：ワークシートのセルの値が変更されたときに発生するイベント
Private Sub Worksheet_Change(ByVal Target As Range)
’セルA1にデータを入力したときに値を表示する。
With Target
If .Row = 1 And .Column = 1 Then
MsgBox "入力された値は" & .Value & "です"
Else
MsgBox "A1にデータを入力してください。"
End If
End With
End Sub